package com.franklin.java.utils;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.flychord.jdk.util.matchcase.TupleCase;
import com.flychord.jdk.util.tuple.Tuple2;
import com.flychord.jdk.util.tuple.TupleAble;
import com.franklin.java.configuration.sql.completion.CompletionSettingState;
import com.franklin.java.configuration.sql.type.TypeMappingState;
import com.franklin.java.entity.sql.ColumnInfo;
import com.franklin.java.entity.sql.MappingType;
import com.franklin.java.entity.sql.ShowDescriptionType;
import com.franklin.plugins.common.collection.PrettyArrayList;
import com.franklin.java.api.psi.data.PsiData;
import com.franklin.java.completion.map.MapData;
import com.franklin.plugins.common.utils.DataCheckUtils;
import com.franklin.plugins.common.utils.StringHelper;
import com.intellij.openapi.project.Project;

import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author Franklin
 * @since 2021/08/22 15:50
 */
public class SqlHelper {

    public final static String UNKNOWN_TABLE = "UNKNOWN";

    protected Project myProject;

    public SqlHelper(Project myProject) {
        this.myProject = myProject;
    }

    /**
     * 解析sql，返回元组(表名,字段名（转驼峰）,别名)<br>
     *  支持更多数据库
     * @param sql:
     * @return java.util.List<com.flychord.jdk.util.tuple.Tuple2<java.lang.String,java.lang.String>>
     * @author Franklin
     * @since 2021/1/28 10:39
     */
    public List<ColumnInfo> getColumns(DbType dbType, String sql) {
        try {
            SQLStatementParser parser = new SQLStatementParser(sql, dbType);
            SQLStatement sqlStatement = parser.parseStatement();
            SchemaStatVisitor visitor = new SchemaStatVisitor(dbType);

            sqlStatement.accept(visitor);

            Collection<TableStat.Column> columns = visitor.getColumns();
            if (DataCheckUtils.isEmpty(columns)) {
                return Collections.emptyList();
            }

            //获取别名
            List<SQLSelectItem> selectList = getOriginSqlColumns(sqlStatement);

            List<Tuple2<String, String>> aliases = selectList
                    .stream()
                    .map(item -> {
                        String originSelect = item.toString();
                        int start = originSelect.indexOf(".");
                        StringBuilder builder = new StringBuilder();
                        String origin = null;
                        if (start >= 0) {
                            for (int i = start + 1; i < originSelect.length(); i++) {
                                char c = originSelect.charAt(i);
                                if (c != ' '){
                                    builder.append(c);
                                }else {
                                    break;
                                }
                            }
                            origin = builder.toString();
                        }else {
                            origin = item.getExpr().toString();
                        }
                        return TupleAble.tuple(origin,item.getAlias());
                    })
                    .collect(Collectors.toList());

            List<ColumnInfo> result = new PrettyArrayList<>();
            int i = 0;
            Map<String, List<TableStat.Column>> columnNameColumnMap = columns
                    .stream()
                    .filter(TableStat.Column::isSelect)
                    .collect(Collectors.groupingBy(TableStat.Column::getName));

            aliases.forEach(
                    TupleCase.match(
                            (columnName,alias) -> {
                                List<TableStat.Column> columnList = columnNameColumnMap.get(columnName);
                                String tableName = null;
                                if (DataCheckUtils.isNotEmpty(columnList)){
                                    TableStat.Column column = columnList.get(0);
                                    tableName = column.getTable();
                                }else {
                                    tableName = UNKNOWN_TABLE;
                                }
                                result.add(new ColumnInfo(tableName, columnName, alias));
                            }
                    )
            );

            return result;
        } catch (Exception e) {
            String message = e.getMessage();
            return Collections.singletonList(new ColumnInfo("SQL "+ message,"",""));
        }
    }

    /**
     * 获取查询对象<br>
     * @author Franklin
     * @since 2021/1/28 15:27
     * @param sqlStatement:
     * @return java.util.List<com.alibaba.druid.sql.ast.statement.SQLSelectItem>
     */
    private List<SQLSelectItem> getOriginSqlColumns(SQLStatement sqlStatement) {
        if (sqlStatement instanceof SQLSelectStatement) {
            SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
            SQLSelect sqlSelect = sqlSelectStatement.getSelect();
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlSelect.getQuery();
            return query.getSelectList();
        }
        return Collections.emptyList();
    }

    /**
     * 解析Mybatis-Plus里Select的SQL片段<br>
     * @author Franklin
     * @since 2021/8/26 16:03
     * @param sqlSelectSegments :
     * @return java.lang.String
     */
    public List<ColumnInfo> resolveMPSqlSelectSegment(Collection<String> sqlSelectSegments,String tableName){
        if (DataCheckUtils.isEmpty(sqlSelectSegments)){
            return Collections.emptyList();
        }

        //伪装成SQL再解析
        String sqlSelectPart = sqlSelectSegments.stream()
            .filter(DataCheckUtils::isNotEmpty)
            .map(sqlSelectSegment -> sqlSelectSegment.substring(1, sqlSelectSegment.length() - 1))
            .collect(Collectors.joining(","));

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder
            .append("select")
            .append(' ').append(sqlSelectPart).append(' ')
            .append("from").append(' ').append(tableName);

        return getColumns(DbType.mysql,sqlBuilder.toString());
    }

    public List<MapData> convertColumnToMapData(Collection<ColumnInfo> columns){
        MappingType mappingType = TypeMappingState.getMappingType(myProject);
        AtomicInteger indexRef = new AtomicInteger(0);
        return Optional.ofNullable(columns)
            .stream()
            .flatMap(Collection::stream)
            .map(col -> {
                col.setColumnIndex(indexRef.getAndIncrement());
                if (Objects.nonNull(columns)) {
                    col.setColumnSum(columns.size());
                }
                return convertColumnToMapData(col,mappingType);
            })
            .collect(Collectors.toList());
    }

    public MapData convertColumnToMapData(String tableName,String columnName,String columnAlias,MappingType mappingType){
        return convertColumnToMapData(new ColumnInfo(tableName,columnName,columnAlias),mappingType);
    }

    /**
     * 将列数据元组(表名，字段名，字段别名)转换为MapData<br>
     * @author Franklin
     * @since 2021/8/26 16:18
     * @param column:
     * @return com.franklin.java.completion.map.MapData
     */
    public MapData convertColumnToMapData(ColumnInfo column, MappingType mappingType){
        String value = column.getColumnName();
        if (DataCheckUtils.isNotEmpty(column.getAlias())) {
            value = column.getAlias();
        }
        //驼峰
        if (CompletionSettingState.isUseCamel(myProject)) {
            value = StringHelper.underlineToCamel(value);
        }
        value = "\"" + value + "\"";

        //类型
        String javaType = resolveJavaType(column.getColumnType(), mappingType);

        PsiData colData = new PsiData(PsiUtils.getSimpleClassName(javaType), value, null, javaType, null);
        MapData mapData = new MapData(
            colData,
            colData,
            null
        );

        mapData.setColumnInfo(column);

        //提示值
        ShowDescriptionType descriptionType = ShowDescriptionType.of(CompletionSettingState.getDescription(myProject));
        switch (descriptionType){
            case NOTHING:mapData.setDescription("");break;
            case COLUMN_COMMENT:mapData.setDescription(column.getComment());break;
            case TABLE_NAME:mapData.setDescription(column.getTableName());break;
            default:mapData.setDescription(column.getComment());
        }

        return mapData;
    }

    public static String resolveJavaType(String columnType,MappingType mappingType){
        return Optional.ofNullable(columnType)
            .filter(DataCheckUtils::isNotEmpty)
            .map(col -> {
                Map<String, String> noLengthMappingMap = mappingType.getNoLengthMappingMap();
                Map<String, String> hasLengthMappingMap = mappingType.getHasLengthMappingMap();
                if (noLengthMappingMap.containsKey(col)){
                    return noLengthMappingMap.get(col);
                }

                //兼容实体类
                if (hasLengthMappingMap.containsKey(col)){
                    return hasLengthMappingMap.get(col);
                }

                //正则匹配
                for (Map.Entry<String, String> entry : hasLengthMappingMap.entrySet()) {
                    Pattern pattern = Pattern.compile(entry.getKey());
                    Matcher matcher = pattern.matcher(col);
                    if (matcher.matches()){
                        return entry.getValue();
                    }
                }
                return "java.lang.String";
            })
            .orElse("java.lang.String");
    }

    public String resolveColumnInGetMethod(String getMethodName){
        if (getMethodName.startsWith("get")){
            getMethodName = getMethodName.substring(3);
        }
        return "\"" + StringHelper.camelToUnderline(getMethodName) + "\"";
    }
}
